package com.system.dao.impl;

import com.system.config.Jdbc;
import com.system.dao.ReplyComment;
import com.system.domin.Comment;
import com.system.untils.FormatOutput;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class ReplyCommentMapper implements ReplyComment {
    private final Jdbc jb;
    private final FormatOutput fo = new FormatOutput();
    private final static String allUse = "WITH RECURSIVE SubReplies AS ("
            + "  SELECT id, parent_id, userName, content, timestamp"
            + "  FROM comments"
            + "  WHERE id = ?"
            + "  UNION ALL"
            + "  SELECT r.id, r.parent_id, r.userName, r.content, r.timestamp"
            + "  FROM comments r"
            + "  INNER JOIN SubReplies sr ON r.parent_id = sr.id"
            + ") "
            + "SELECT * FROM SubReplies";

    public ReplyCommentMapper() {
        this.jb = new Jdbc();
    }


    // 查看更多评论
    @Override
    public void searchAllCurrentID(int currentPage, int parentCommentId) {
        try {
            int pageSize = 5;
            int offset = (currentPage - 1) * pageSize; // 计算查询的偏移量
            String sql = "WITH RECURSIVE SubReplies AS (" +
                    "  SELECT id, parent_id, userName, content, timestamp" +
                    " FROM comments WHERE id = ? " +
                    "  UNION ALL " +
                    "  SELECT r.id, r.parent_id, r.userName, r.content, r.timestamp" +
                    " FROM comments r INNER JOIN SubReplies sr ON r.parent_id = sr.id " +
                    ") " +
                    "SELECT * FROM SubReplies LIMIT ? OFFSET ?";
            PreparedStatement pstmt = jb.prepareStatement(sql);
            pstmt.setInt(1, parentCommentId); // 设置父评论ID
            pstmt.setInt(2, pageSize); // 设置每页显示的记录数
            pstmt.setInt(3, offset); // 设置偏移量

            ResultSet rs = pstmt.executeQuery();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            int index = offset; // 从偏移量开始计数
            while (rs.next()) {
                String name = rs.getString("userName");
                String text = rs.getString("content");
                String time = formatter.format(rs.getTimestamp("timestamp"));

                // 根据 text 长度显示内容
                if (text.length() > 20) {
                    System.out.println("------------------------------------------------------------");
                    System.out.println(" "+(++index) +"  "+name+":");
                    System.out.println(" "+ fo.formatStringByLength(text, 20)+"\t --> "+time);
                    System.out.println("------------------------------------------------------------");
                } else {
                    System.out.println("------------------------------------------------------------");
                    System.out.println(" "+(++index) +"  "+name+":");
                    System.out.println(" "+text+"\t --> "+time);
                    System.out.println("------------------------------------------------------------");
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 统计该parentID下子回复
    @Override
    public int countParentAmounts(int commentID) {
        int count = 0;
        try {
            try (PreparedStatement stmt = this.jb.prepareStatement(allUse)) {
                stmt.setInt(1, commentID); // 设置查询的起始ID
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        int id = rs.getInt("id");
                        count++;
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }

    // 添加回复评论
    @Override
    public boolean addReplyComment(int commentID, String email,String text) {
        try {
            String publishTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
            Comment comment = new Comment(getToEmail(commentID),getPublishID(commentID),getName(email),text,publishTime,commentID);
            String sql = "insert into comments(email,publish_id,userName,content,timestamp,parent_id,status,unread,aimEmail) values(?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = this.jb.prepareStatement(sql);
            pstmt.setString(1, comment.getEmail());  // 目标
            pstmt.setInt(2,comment.getPublishID());
            pstmt.setString(3,comment.getUserName());
            pstmt.setString(4,comment.getContext());
            pstmt.setString(5,comment.getTimestamp());
            pstmt.setInt(6,comment.getParent_id());
            pstmt.setString(7,comment.getStatus());
            pstmt.setInt(8,comment.getUnread());
            pstmt.setString(9,email);
            int i = pstmt.executeUpdate();
            return i > 0;
        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
    }
    public String getToEmail(int  commentId) {
        try {
            String sql = "select * from comments where id='"+commentId+"'";
            ResultSet rs = jb.getStmt().executeQuery(sql);
            if (rs.next())
            {
                return rs.getString("email");
            }

        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public String getName(String email) {
        try {
            String sql = "select * from memouser where email='"+email+"'";
            ResultSet rs = jb.getStmt().executeQuery(sql);
            if (rs.next())
            {
                return rs.getString("name");
            }

        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public Map<Integer, Integer> lockReplyID(int id) {
            Map<Integer, Integer> replyMap = new HashMap<>();
            try {
                int index = 0;
                try (PreparedStatement stmt = this.jb.prepareStatement(allUse)) {
                    stmt.setInt(1, id); // 设置查询的起始ID
                    try (ResultSet rs = stmt.executeQuery()) {
                        while (rs.next()) {
                            int replyId = rs.getInt("id");
                            replyMap.put(++index, replyId);
                        }
                    }
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return replyMap;
    }

    @Override
    public void selectAllChildComment(int commentID) {
        try {
            int count = 0;
            int index = 0;
            try (PreparedStatement stmt = this.jb.prepareStatement(allUse)) {
                stmt.setInt(1, commentID); // 设置查询的起始ID
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if (count == 5)
                        {
                            break;
                        }
                        int id = rs.getInt("id");
                        int parentId = rs.getInt("parent_id");
                        String userName = rs.getString("userName");
                        String content = rs.getString("content");
                        String timestamp = rs.getString("timestamp");
                        // 打印查询结果的详细信息
                        if(count == 0 && content.length() > 20)
                        {
                            System.out.println("------------------------------------------------------------");
                            System.out.println(" "+(++index) +"  "+userName+" 回复 " +getUserNameFirst(parentId) +":");
                            System.out.println(" "+ fo.formatStringByLength(content, 20)+"\t-->"+timestamp);
                            System.out.println("------------------------------------------------------------");
                            count++;
                        }
                        if (content.length() > 20) {
                            // 第一个需要处理
                            System.out.println("------------------------------------------------------------");
                            System.out.println(" "+(++index) +"  "+userName+" 回复 " +getUserName(parentId) +":");
                            System.out.println(" "+ fo.formatStringByLength(content, 20)+"\t-->"+timestamp);
                            System.out.println("------------------------------------------------------------");
                            count++;
                        } else {
                            System.out.println("------------------------------------------------------------");
                            System.out.println(" "+(++index) +"  "+userName+" 回复 " +getUserName(parentId) +":");
                            System.out.println(" "+content+"\t-->"+timestamp);
                            System.out.println("------------------------------------------------------------");
                            count++;
                        }

                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public int getPublishID(int commentID)
    {
        int publishID = 0;
        try {
            String sqlGetPublishID = "select publish_id from comments where id = ?";
            PreparedStatement pstmt = jb.prepareStatement(sqlGetPublishID);
            pstmt.setInt(1, commentID);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                publishID = rs.getInt("publish_id");
            }
        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        return publishID;
    }
    public String getEmail(int commentID)
    {
        try {
            String sqlGetPublishID = "select email from publish_tb where id = ?";
            PreparedStatement pstmt = jb.prepareStatement(sqlGetPublishID);
            pstmt.setInt(1, commentID);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("email");
            }
        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        return null;
    }
    public String getUserName(int parent_id)
    {
        try {
            String sql = "select userName from comments where id = ?";
            PreparedStatement pstmt = jb.prepareStatement(sql);
            pstmt.setInt(1, parent_id);
            pstmt.executeQuery();
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("userName");
            }
        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        return null;
    }
    public String getUserNameFirst(int id)
    {
        try {
            String sql = "select name from publish_tb where id = ?";
            PreparedStatement pstmt = jb.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeQuery();
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("name");
            }
        }catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        return null;
    }
}



